"""
author : wlingforever
file name : count
last modified time:2022.11.15

description:
    count detect result

"""
import pymysql

# from pymysql import MySQLError
# 建立连接
# host 主机 port 端口
# charset=字符集

username = 'rdmanager'
ur_password = 'rdm2022.'
# username = 'rdquery'
# ur_password = 'rdquery2022'

pnw = 'localhost'
#
# pnw = '100.126.31.240' # lib iv4 (yes)

# pnw = '110.53.160.18' # lib pub (can not connect)
#
# pnw = '100.65.255.40' # rom iv4
# pnw = '175.13.175.247' # rom pub

# pnw = '100.67.113.157' # LIB CSU iv4
# pnw = '175.11.249.143' # LIB CSU pub (yes)will be changed ever change the connection time


coon = pymysql.connect(host=pnw, port=3306,
                       user=username, password=ur_password,
                       database='rfo_detection', charset='utf8mb4')

try:
    # print(coon)
    # 获取游标对象
    with coon.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
        # 通过对象向数据库发sql语句，获取执行结果
        """
        sql_select = "select key_id,image_name, image_path,detect_time from tb_i_data "
        cursor.execute(sql_select)
        # result is a dict
        # print(cursor.fetchone())
        image = []
        for row in iter(lambda: cursor.fetchone(), None):
            image.append(row)
        # row = cursor.fetchone()
        # while row:
        #     image.append(row)
        #     row = cursor.fetchone()
        print("images find成功!")
        print(len(image))
        """

        class_set = {'24', '17', '36', '6', '7', '63', '67', '25', '10', '16', '19', '0', '5', '8', '26', '2', '18',
                     '9', '14'}
        print('cla_num', 'cla_name','numbers','\tconf_av')
        for class_t in class_set:
            # get targets
            # 通过对象向数据库发sql语句，获取执行结果
            sql_select = "select key_id,target_pn,class,cls_num,xywh,conf,target_iname from tb_o_data " \
                         "where cls_num = %s and conf > '0.5'"
            cursor.execute(sql_select,class_t)
            # print(cursor.fetchone())
            targets = []
            conf_list = []
            for row in iter(lambda: cursor.fetchone(), None):
                targets.append(row)
                conf_list.append(row['conf'])
                # print(row)

            # row = cursor.fetchone()
            # while row:
            #     image.append(row)
            #     row = cursor.fetchone()
            conf_list = [float(x) for x in conf_list]
            conf_av = sum(conf_list)/len(conf_list)
            print(class_t, '\t\t', row['class'], '\t\t\t\t', len(targets),'\t\t','\t',conf_av)

        print("database operation complete!")

except pymysql.MySQLError as err:
    print("query error")
    print(err)

finally:
    coon.close()
